<?php
namespace app\controller;

use think\facade\Db;
use think\facade\Filesystem;
use think\Request;
use think\facade\View;

class Test
{


    public function export()
    {
        $data = Db::name('survey_answer')
            ->where('class_id', '=', 524)
            ->where('survey_id', '=', 69)
            ->where('test_type', '=', 2)
            ->field('userid, subject_id, answer')
            ->select()
            ->toArray();

        foreach ($data as &$v) {
            $subject_name = Db::name('courseware_survey_subject')
                ->where('survey_id' , '=', 69)
                ->where('id', '=', $v['subject_id'])
                ->value('subject');
            $v['subject_name'] = $subject_name;

            $option = Db::name('courseware_survey_option')
                ->where('survey_id' , '=', 69)
                ->where('subject_id', '=', $v['subject_id'])
                ->where('id', '=', $v['answer'])
                ->field('title, letter')
                ->find();
            $v['option_name'] = $option['title'];
            $v['letter'] = $option['letter'];
        }

        pre($data);
    }

    public function tt(Request $request)
    {
        //$str = 'qhbRGThcfUdibZk/mYEIFRnZguhV1YJqeWL0VlXdPkeNnk2cz7Yhk5dGr2gCqXobL2APGX1nIKGSamaD4oa+Wzq/h6v42NRIZssFMOGFV+pmRVayaG2sJML2ppZjm2+LNTFRxlSHUYKOT7Hr2ME/Yx3r9eLEriZlNzEqNwwRv614EFbn3+DSxluldglY28Qv9OtXcqZ7Cz1mkx0ingoFUg==';
        //$str = '0j8iMxnHZkEbCYoPjxupmyG2vYuOtCOo+Jxdkd1REiS3Kb1kyZHKkIs4x7XY2FjXtUnzyogtEs5wvwdFfE5kKfH4/+gw0W/JPKdE+OkB93ms7dwNvHEcfd3Gjyz5LO58DjRNgkQuH+F9+7FSseG5Zz7ShmOBc7NmzNDOrxKW2RBsKfJb5xvWgCROL4/2UWzm';
        $str = '{"name":"\u987e\u4f73\u8273","phone":"18516565270","address":"\u4e0a\u6d77\u5e02\u9759\u5b89\u533a\u5357\u4eac\u897f\u8def1601\u53f7\u8d8a\u6d0b\u5e7f\u573a25\u697c"}';
        $res = is_null(json_decode($str, true));
        if ($res) {
            $express_json = json_decode(decrypts($str), true);
            $res1 = is_array($express_json);
            if (!$res1) {
                $express = json_decode($express_json,true);
            } else {
                $express = $express_json;
            }

        } else {
            $express = json_decode($str,true);
        }
        pre($express);
    }

    protected function letter($key)
    {
        $data = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE'];
        return $data[$key];
    }

    protected function getMillisecond() {
        list($t1, $t2) = explode(' ', microtime());
        return (float)sprintf('%.0f',(floatval($t1)+floatval($t2))*1000);
    }
    public function t()
    {
        $rows = Db::table('o_live')
            ->where('id', '=', 353)
            ->select()
            ->toArray();

        foreach ($rows as &$v) {
            //毫秒级时间戳
            $timestamp = $this->getMillisecond();

            //签名
            $string = md5(config('app.zhibo.AppSecret').'appId'.config('app.zhibo.appId').'channelId'.$v['channelId'].'timestamp'.$timestamp.config('app.zhibo.AppSecret'));
            $sign = strtoupper($string);
            $url = 'http://api.polyv.net/live/v3/channel/basic/update?appId='.config('app.zhibo.appId').'&channelId='.$v['channelId'].'&sign='.$sign.'&timestamp='.$timestamp;

            //请求参数
            $data = array(
                'authSettings' => array(
                    array(
                        'rank' => 1,
                        'enabled' => 'Y',
                        'externalUri' => config('app.zhibo.url').'/qy_oauth2/externalAuth',
                        'externalRedirectUri' => config('app.zhibo.url').'/qy_oauth2/open',
                    ),
                ),
            );
            //pre($data);
            $json_data = json_encode($data);

            // 设置请求头
            $head = array(
                'content-type: application/json'
            );
            // 编辑频道
            $res = zhibo_http_post($url, $json_data, false, $head);
            pre($res);
        }
    }

    public function test1()
    {

        $data = Db::name('courseware')->select();
        foreach ($data as &$v) {
            if (!empty($v['path'])) {
                if (strpos($v['path'],'https') === false) {
                    $blob = new Pub();
                    $path = str_replace(' ', '', 'https://adminlearningsparkminiapp.lorealchina.com/'.$v['path']);

                    $icon =  strrchr($path,'.');

                    if (strtolower($icon) == '.pdf') {
                        $accept = 'application/pdf';
                    } else if (strtolower($icon) == '.doc') {
                        $accept = 'application/msword';
                    } else if (strtolower($icon) == '.docx') {
                        $accept = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document';
                    } else if (strtolower($icon) == '.jpg') {
                        $accept = 'image/jpg';
                    } else if (strtolower($icon) == '.png') {
                        $accept = 'image/png';
                    } else if (strtolower($icon) == '.jpeg') {
                        $accept = 'image/jpeg';
                    } else if (strtolower($icon) == '.mp4') {
                        $accept = 'video/mp4';
                    } else if (strtolower($icon) == '.mp3') {
                        $accept = 'audio/mp3';
                    } else if (strtolower($icon) == '.mov') {
                        $accept = 'audio/x-m4a';
                    }

                    $res = $blob->uploadBlob($path, $path, $accept, 'uploads');
                    if ($res['code'] == 200) {
                        $url = $res['data'];
                        Db::name('courseware')->where('id', '=', $v['id'])->update(['path' => $url]);
                    }
                }
            }
        }
    }

    public function test()
    {
        $date = date('YmdHis', 1669049458);
        $file_path = root_path() . "/public/csv/classes/ClassesRecord-".$date.".csv";


        if (!file_exists($file_path)) {
            $fp = fopen($file_path,'a');

            //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));

            //添加导出标题
            fputcsv($fp, ['班级名称','课程名称','员工工号','员工姓名', 'Division', '部门', '注册方式','签到日期','签到时间','开始学习时间','学习时长(S)','学习时长(H)']);
        } else {
            $fp = fopen($file_path,'a');

            //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
        }
        $data = [];
        $where = [];

        if (!empty($data['status'])) {
            if ($data['status'] == 1) {
                $where[] = ['a.start', '>', time()];

            }elseif ($data['status'] == 2) {
                $where[] = ['a.start', '<=', time()];
                $where[] = ['a.end', '>=', time()];

            }elseif ($data['status'] == 3) {
                $where[] = ['a.end', '<', time()];
            }
        }

        if (!empty($data['time'])) {
            $where[] = ['a.start', 'between', [strtotime($data['time']), strtotime($data['time'])+86399]];
        }

        if (!empty($get['time1'])) {
            $where[] = ['a.end', 'between', [strtotime($data['time1']), strtotime($data['time1'])+86399]];
        }

        if (!empty($get['keys'])) {
            $where[] = ['a.teacher_name|a.address|a.title|a.e_title|b.e_seminar', 'like', '%'.$get['keys'].'%'];
        }

        if (!empty($get['courseid'])) {
            $where[] = ['a.courseid', '=', $get['courseid']];
        }

        $where[] = ['a.status', 'in','1,2'];


        $ids = Db::table('o_classes')
            ->alias('a')
            ->join('o_course b','a.courseid=b.courseid', 'left')
            ->where($where)
            ->column('a.id');

        $subQuery1 = Db::table('o_classes_log')
            ->field('SUM(learning_time) as time, userid')
            ->group('userid')
            ->buildSql();

        $members = Db::name('classes_user')
            ->alias('a')
            ->join('o_members b', 'b.userid=a.userid', 'left')
            ->join('o_course c', 'c.courseid=a.courseid', 'left')
            ->join('o_classes d', 'd.id=a.classes_id', 'left')
            ->join($subQuery1.'e','e.userid=a.userid','left')
            ->whereIn('a.classes_id', $ids)
            ->where('b.status', '=', 1)
            ->field('a.userid, a.classes_id, a.type, b.name, b.division, b.department_name, c.seminar, d.title, e.time')
            ->page(1, 10)
            ->select()
            ->toArray();

        if ($members) {
            foreach($members as &$v) {
                $inf = $v;

                $count = mb_strlen($inf['userid']);
                if ($count == 6) {
                    if (is_numeric($inf['userid'])) {
                        $userid = "00".$inf['userid'];
                    } else {
                        $userid = $inf['userid'];
                    }
                } else {
                    $userid = $inf['userid'];
                }
                $class_log = Db::name('classes_log')
                    ->where('userid', '=', $inf['userid'])
                    ->where('classes_id', '=', $inf['classes_id'])
                    ->order('add_time', 'asc')
                    ->find();
                $sign_log = Db::name('classes_log')
                    ->where('userid', '=', $inf['userid'])
                    ->where('classes_id', '=', $inf['classes_id'])
                    ->where('resource_id', '=', 0)
                    ->find();

                $array = [
                    $inf['title'],
                    $inf['seminar'],
                    $userid,
                    $inf['name'],
                    $inf['division'],
                    $inf['department_name'],
                    $inf['type'] == 1 ? '后台导入':'现场注册',
                    isset($sign_log['qiandao']) ? date('Y-m-d', $sign_log['add_time']) : '未签到',
                    isset($sign_log['qiandao']) ? date('H:i:s', $sign_log['add_time']) : '未签到',
                    (isset($class_log) ? date('Y-m-d H:i:s', $class_log['add_time']) : ''),
                    $inf['time'],
                    number_format($inf['time']/3600, 2)
                ];

                fputcsv($fp, $array);
            }
            return ['code' => 0, 'ts' => $data['ts'], 'download_id' => $data['download_id'], 'page' => ($data['page'] + 1), 'time' => $data['time'], 'time1' => $data['time1'], 'status' => $data['status'], 'courseid' => $data['courseid'], 'keys' => $data['keys']];

        } else {

            return ['code' => 1];
        }
    }
    public function class_resource()
    {
        $data = Db::name('step_resource')->where('step_id', '<>', 0)->select()->toArray();

        foreach ($data as &$v) {
            $class_step = Db::name('class_step')->where('id', '=', $v['step_id'])->find();
            $insert = array(
                'id' => $class_step['id'],
                'title' => $v['title'],
                'duration' => $v['duration'],
                'download' => $v['download'],
                'user_id' => 1,
                'path' => $v['path'],
                'content' => $v['content'],
                'add_time' => $v['add_time'],
                'update_time' => $v['update_time'],
            );


            if ($v['type'] == 4) {
                $insert['type'] = 3;

            } elseif ($v['type'] == 5) {
                $insert['type'] = 4;

            } elseif ($v['type'] == 6) {
                $insert['type'] = 5;
            } else {
                $insert['type'] = $v['type'];
            }

            Db::name('courseware')->insert($insert);

            if ($v['type'] == 4) {

                $test = Db::name('step_test')
                    ->where('step_id', '=', $class_step['id'])
                    ->where('type', '=', 0)
                    ->find();

                if ($test) {
                    $insert = array(
                        'id' => $test['id'],
                        'courseware_id' => $class_step['id'],
                        'title' => $test['title'],
                        'is_duration' => $test['is_duration'],
                        'duration' => $test['duration'],
                        'limits' => $test['limits'],
                        'score' => $test['score'],
                        'fpy' => $test['fpy'],
                        'is_number' => $test['is_number'],
                        'number' => $test['number'],
                        'is_number_read' => $test['is_number_read'],
                        'sum_score' => $test['sum_score'],
                        'add_time' => $test['add_time'],
                    );

                    Db::name('courseware_test')->insert($insert);

                    $question = Db::name('step_question')
                        ->where('test_id', '=', $test['id'])
                        ->select()
                        ->toArray();

                    foreach ($question as &$qv) {
                        $insert = array(
                            'id' => $qv['id'],
                            'test_id' => $test['id'],
                            'title' => $qv['title'],
                            'q_score' => $qv['q_score'],
                            'img' => $qv['img'],
                            'a_type' => $qv['a_type'],
                            'check' => $qv['check'],
                            'remark' => $qv['remark'],
                            'answer' => $qv['answer'],
                            'add_time' => $qv['add_time'],
                        );
                        Db::name('courseware_test_question')->insert($insert);
                    }

                    $where = array(
                        'test_id' => $test['id']
                    );

                    Db::name('test_answer')->where($where)->update(['type' => 0]);
                    Db::name('test_submit')->where($where)->update(['type' => 0]);
                }

            } elseif ($v['type'] == 5) {

                $survey = Db::name('step_survey')->where('step_id', '=', $v['id'])->where('type', '=', 1)->find();

                $insert = array(
                    'id' => $survey['id'],
                    'courseware_id' => $class_step['id'],
                    'title' => $survey['title'],
                    'images' => $survey['images'],
                    'description' => $survey['description'],
                    'survey_result' => $survey['survey_result'],
                    'end_time' => $survey['end_time'],
                    'relation' => $survey['relation'],
                    'add_time' => $survey['add_time'],
                    'update_time' => $survey['update_time'],
                );
                Db::name('courseware_survey')->insert($insert);

                $subject = Db::name('step_survey_subject')->where('survey_id', '=', $survey['id'])->select()->toArray();

                foreach ($subject as &$val) {
                    $insert = array(
                        'id' => $val['id'],
                        'survey_id' => $val['survey_id'],
                        'subject' => $val['subject'],
                        'images' => $val['images'],
                        'other' => $val['other'],
                        'min' => $val['min'],
                        'max' => $val['max'],
                        'character_min' => $val['character_min'],
                        'character_max' => $val['character_max'],
                        'height' => $val['height'],
                        'subject_type' => $val['subject_type'],
                        'add_time' => $val['add_time'],
                        'update_time' => $val['update_time'],
                    );
                    Db::name('courseware_survey_subject')->insert($insert);

                    $option = Db::name('step_survey_option')->where('survey_id', '=', $val['survey_id'])->where('subject_id', '=', $val['id'])->select()->toArray();

                    foreach ($option as &$value) {
                        $insert = array(
                            'id' => $value['id'],
                            'survey_id' => $value['survey_id'],
                            'subject_id' => $value['subject_id'],
                            'title' => $value['title'],
                            'option_images' => $value['option_images'],
                            'letter' => $value['letter'],
                            'update_time' => $value['update_time'],
                        );
                        Db::name('courseware_survey_option')->insert($insert);
                    }
                }

                $where = array(
                    'survey_id' => $survey['id']
                );

                Db::name('survey_answer')->where($where)->update(['test_type' => 1]);
                Db::name('survey_submit')->where($where)->update(['type' => 1]);
            }
            $insert = array(
                'id' => $class_step['id'],
                'classes_id' => $v['classes_id'],
                'step_id' => $class_step['step'],
                'courseware_id' => $class_step['id'],
                'add_time' => $v['add_time'],
            );


            if ($v['type'] == 6) {
                $insert['status'] = 3;
            } else {
                $insert['status'] = 1;
            }

            Db::name('classes_step_resource')->insert($insert);

        }
    }


    public function learning_resource()
    {
        $data = Db::name('learning_resource')->select()->toArray();

        foreach ($data as &$v) {
            $insert = array(
                'title' => $v['title'],
                'duration' => $v['duration'],
                'download' => $v['download'],
                'user_id' => 1,
                'path' => $v['path'],
                'content' => $v['content'],
                'add_time' => $v['add_time'],
                'update_time' => $v['update_time'],
            );


            if ($v['type'] == 4) {
                $insert['type'] = 3;

            } elseif ($v['type'] == 7) {
                $insert['type'] = 4;

            } elseif ($v['type'] == 6) {
                $insert['type'] = 5;
            } else {
                $insert['type'] = $v['type'];
            }

           $courseware_id = Db::name('courseware')->insertGetId($insert);

            if ($v['type'] == 4) {

                $test = Db::name('step_test')
                    ->where('step_id', '=', $v['id'])
                    ->where('type', '=', 1)
                    ->find();

                if ($test) {
                    $insert = array(
                        'id' => $test['id'],
                        'courseware_id' => $courseware_id,
                        'title' => $test['title'],
                        'is_duration' => $test['is_duration'],
                        'duration' => $test['duration'],
                        'limits' => $test['limits'],
                        'score' => $test['score'],
                        'fpy' => $test['fpy'],
                        'is_number' => $test['is_number'],
                        'number' => $test['number'],
                        'is_number_read' => $test['is_number_read'],
                        'sum_score' => $test['sum_score'],
                        'add_time' => $test['add_time'],
                    );

                    Db::name('courseware_test')->insert($insert);

                    $question = Db::name('step_question')
                        ->where('test_id', '=', $test['id'])
                        ->select()
                        ->toArray();

                    foreach ($question as &$qv) {
                        $insert = array(
                            'id' => $qv['id'],
                            'test_id' => $test['id'],
                            'title' => $qv['title'],
                            'q_score' => $qv['q_score'],
                            'img' => $qv['img'],
                            'a_type' => $qv['a_type'],
                            'check' => $qv['check'],
                            'remark' => $qv['remark'],
                            'answer' => $qv['answer'],
                            'add_time' => $qv['add_time'],
                        );

                        Db::name('courseware_test_question')->insert($insert);
                    }

                    $where = array(
                        'test_id' => $test['id']
                    );

                    Db::name('test_answer')->where($where)->update(['type' => 1]);
                    Db::name('test_submit')->where($where)->update(['type' => 1]);
                }

            } elseif ($v['type'] == 7) {

                $survey = Db::name('step_survey')->where('step_id', '=', $v['id'])->where('type', '=', 2)->find();

                $insert = array(
                    'id' => $survey['id'],
                    'courseware_id' => $courseware_id,
                    'title' => $survey['title'],
                    'images' => $survey['images'],
                    'description' => $survey['description'],
                    'survey_result' => $survey['survey_result'],
                    'end_time' => $survey['end_time'],
                    'relation' => $survey['relation'],
                    'add_time' => $survey['add_time'],
                    'update_time' => $survey['update_time'],
                );
                Db::name('courseware_survey')->insert($insert);

                $subject = Db::name('step_survey_subject')->where('survey_id', '=', $survey['id'])->select()->toArray();

                foreach ($subject as &$val) {
                    $insert = array(
                        'id' => $val['id'],
                        'survey_id' => $val['survey_id'],
                        'subject' => $val['subject'],
                        'images' => $val['images'],
                        'other' => $val['other'],
                        'min' => $val['min'],
                        'max' => $val['max'],
                        'character_min' => $val['character_min'],
                        'character_max' => $val['character_max'],
                        'height' => $val['height'],
                        'subject_type' => $val['subject_type'],
                        'add_time' => $val['add_time'],
                        'update_time' => $val['update_time'],
                    );
                    Db::name('courseware_survey_subject')->insert($insert);

                    $option = Db::name('step_survey_option')->where('survey_id', '=', $val['survey_id'])->where('subject_id', '=', $val['id'])->select()->toArray();

                    foreach ($option as &$value) {
                        $insert = array(
                            'id' => $value['id'],
                            'survey_id' => $value['survey_id'],
                            'subject_id' => $value['subject_id'],
                            'title' => $value['title'],
                            'option_images' => $value['option_images'],
                            'letter' => $value['letter'],
                            'update_time' => $value['update_time'],
                        );
                        Db::name('courseware_survey_option')->insert($insert);
                    }
                }

                $where = array(
                    'survey_id' => $survey['id']
                );

                Db::name('survey_answer')->where($where)->update(['test_type' => 2]);
                Db::name('survey_submit')->where($where)->update(['type' => 2]);
            }
            $insert = array(
                'id' => $v['id'],
                'learning_id' => $v['class_id'],
                'step_id' => $v['unit_id'],
                'courseware_id' => $courseware_id,
                'add_time' => $v['add_time'],
            );


            if ($v['type'] == 6) {
                $insert['status'] = 3;
            } else {
                $insert['status'] = 1;
            }

            Db::name('learning_step_resource')->insert($insert);
        }
    }


    public function live_resource()
    {
        $data = Db::name('zhibo_resource')->select()->toArray();

        foreach ($data as &$v) {
            $insert = array(
                'title' => $v['title'],
                'duration' => 0,
                'download' => $v['download'],
                'type' => $v['type'],
                'user_id' => 1,
                'path' => $v['path'],
                'content' => $v['content'],
                'add_time' => $v['add_time'],
                'update_time' => $v['update_time'],
            );


            $courseware_id = Db::name('courseware')->insertGetId($insert);
            $insert = array(
                'id' => $v['id'],
                'live_id' => $v['zhibo_id'],
                'step_id' => $v['unit_id'],
                'courseware_id' => $courseware_id,
                'add_time' => $v['add_time'],
            );


            if ($v['type'] == 6) {
                $insert['status'] = 3;
            } else {
                $insert['status'] = 1;
            }

            Db::name('live_step_resource')->insert($insert);
        }
    }

}